import { sql } from '@vercel/postgres';
import {
  CustomerField,
  CustomersTableType,
  InvoiceForm,
  InvoicesTable,
  LatestInvoiceRaw,
  User,
  Revenue,
} from './definitions';
import { formatCurrency } from './utils';
import {unstable_noStore as noStore} from 'next/cache'
import {GET} from './client';
import { invoices } from './placeholder-data';

export async function fetchRevenue() {
  noStore()
  console.log('Fetching revenue data...');
  //With dynamic rendering, your application is only as fast as your slowest data fetch.
  await new Promise((resolve) => setTimeout(resolve, 3000));
  const data= GET('/revenue/all')
  console.log('Data fetch completed after 3 seconds.');
  return data
}

export async function fetchLatestInvoices() {
  noStore()
  const data = await GET('/latestInvoice')
  return data.map((item: LatestInvoiceRaw)=>{
    return {
      ...item, amount: formatCurrency(item.amount)
    }})
}

export async function fetchCardData() {
  noStore()
  return GET('/cardData')
}

const ITEMS_PER_PAGE = 6;

export async function fetchFilteredInvoices(
  query: string,
  currentPage: number,
) {
  const offset = (currentPage - 1) * ITEMS_PER_PAGE;
  noStore()
  return GET('/filteredInvoices',{
    query,limit:ITEMS_PER_PAGE,offset
  })
}

export async function fetchInvoicesPages(query: string) {
  noStore()
  const totalInvoice= await GET('/filteredInvoicesPages',{
    query
  })
  return Math.ceil(totalInvoice/ITEMS_PER_PAGE)
}



export async function fetchCustomers() {
  noStore()
  return GET('/customers')
}

export async function fetchInvoiceById(id:any) {
  noStore()
  const invoice = await GET(`/invoice/${id}`)
  console.log('invoice: '+invoice)
  if(invoice){
    return {...invoice,amount: invoice.amount/100}
  }else{
    return null
  }
}

export async function fetchFilteredCustomers(query: string) {
  try {
    const data = await sql<CustomersTableType>`
		SELECT
		  customers.id,
		  customers.name,
		  customers.email,
		  customers.image_url,
		  COUNT(invoices.id) AS total_invoices,
		  SUM(CASE WHEN invoices.status = 'pending' THEN invoices.amount ELSE 0 END) AS total_pending,
		  SUM(CASE WHEN invoices.status = 'paid' THEN invoices.amount ELSE 0 END) AS total_paid
		FROM customers
		LEFT JOIN invoices ON customers.id = invoices.customer_id
		WHERE
		  customers.name ILIKE ${`%${query}%`} OR
        customers.email ILIKE ${`%${query}%`}
		GROUP BY customers.id, customers.name, customers.email, customers.image_url
		ORDER BY customers.name ASC
	  `;

    const customers = data.rows.map((customer) => ({
      ...customer,
      total_pending: formatCurrency(customer.total_pending),
      total_paid: formatCurrency(customer.total_paid),
    }));

    return customers;
  } catch (err) {
    console.error('Database Error:', err);
    throw new Error('Failed to fetch customer table.');
  }
}

export async function getUser(email: string) {
  try {
    const user = await sql`SELECT * FROM users WHERE email=${email}`;
    return user.rows[0] as User;
  } catch (error) {
    console.error('Failed to fetch user:', error);
    throw new Error('Failed to fetch user.');
  }
}
